﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;

namespace Truextend.Training.Dao.Core.SqlServer
{
    /// <summary>
    /// Contains implementation of Dao Service
    /// </summary>
    public class DaoService : IDaoService
    {
        SqlConnection connection = null;
        SqlCommand command = null;
        SqlTransaction transaction = null;

        #region IDaoService Members

        public object GetDBSession()
        {
            throw new NotImplementedException();
        }

        public void BeginTransaction()
        {
            transaction = connection.BeginTransaction();
        }

        public void CommitTransaction()
        {
            transaction.Commit();
        }

        public void RollbackTransaction()
        {            
            transaction.Rollback();
        }

        public bool IsClosed()
        {
            return connection == null ? false : true;
        }

        public object ExecuteQuery(string query, params object[] parameters)
        {
            SqlDataReader res = null;
            command.Transaction = transaction;
            command.CommandText = query;
            SetParams(command, parameters);
            res = command.ExecuteReader();
            return res;
        }

        public object ExecuteInsertQuery(string query, params object[] parameters)
        {
            command.Transaction = transaction;
            command.CommandText = query;
            SetParams(command, parameters);
            return command.ExecuteScalar();
        }

        public int ExecuteNonQuery(string query, params object[] parameters)
        {
            
            command.Transaction = transaction;
            command.CommandText = query;
            SetParams(command, parameters);
            return command.ExecuteNonQuery();            
        }

        public void Open()
        {
            string chainConnection = @"Data Source=.\SQLEXPRESS;Initial Catalog=DBCourses;Integrated Security=True";//ConfigurationManager.AppSettings["connectionString"];
            connection = new SqlConnection(chainConnection);
            command = connection.CreateCommand();
            connection.Open();
        }

        public void Close()
        {
            connection.Close();
        }

        #endregion

        #region Private Methods

        /// <summary>
        /// Set parameters in sql query
        /// </summary>
        private void SetParams(SqlCommand commandExec, params object[] parameters)
        {
            if (parameters != null)
            {
                if (parameters.Length > 0)
                {
                    int position = 0;
                    foreach (object p in parameters)
                    {
                        commandExec.Parameters.AddWithValue("@" + position, p.ToString());
                        position++;
                    }
                }
            }
        }
        
        #endregion
    }
}
